﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Configuration;
using Zxb.DayWork.Core;

namespace Zxb.DayWork.Logic
{
    public class DAL
    {
        public static void SimpleInsert(Database db, string sql)
        {
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.ExecuteNonQuery(cmd);
        }

        public static DataTable ReadDT(Database db, DbCommand cmd)
        {
            DataTable dt = null;
            dt = db.ExecuteDataSet(cmd).Tables[0];
            return dt;
        }

        public static DataTable ReadDtPage(NorthSouth ns, string sql)
        {
            DataTable dt = null;
            Database sourcedb = Constant.DicNorthSouth[ns].DataBase;
            DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
            dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
            return dt;
        }

        public static DataTable ReadDt_Retry_ReturnNewIfNull(Database db, string sql)
        {
            //DataTable dt = null;
            //try
            //{
            //    DbCommand dbCommand = db.GetSqlStringCommand(sql);
            //    dbCommand.CommandTimeout = 120;
            //    dt = db.ExecuteDataSet(dbCommand).Tables[0];
            //}
            //catch (Exception ex)
            //{
            //    Logger.InfoLog.Error("ReadDt_ReturnNewIfNull出错：" + ex.Message + " sql:" + sql);
            //    dt = new DataTable();
            //}
            DataTable dt = ReadDt_Retry(db, sql);
            if (dt == null)
            {
                dt = new DataTable();
            }
            return dt;
        }
        public static DataTable ReadDt_Retry(Database db, string sql)
        {
            int retry = 5;
            DataTable dt = null;
            for (int i = 0; i < retry; i++)
            {
                try
                {
                    DbCommand dbCommand = db.GetSqlStringCommand(sql);
                    dbCommand.CommandTimeout = 120;
                    dt = db.ExecuteDataSet(dbCommand).Tables[0];
                }
                catch (Exception ex)
                {
                    Logger.InfoLog.Info("ReadDt_Retry出错：" + ex.Message + " sql:" + sql + " retry:" + i);
                }
                if (dt != null)
                {
                    break;
                }

            }
            if (dt == null)
            {
                Logger.InfoLog.Error("ReadDt_Retry出错,sql:" + sql);
            }
            return dt;
        }

        public static bool TVPUpdate(DataTable dt, string procedureName, string udtName)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return false;
            }

            bool flag = true;

            using(SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ZxbWriter"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(procedureName, sqlConn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 120;

                SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpNew", dt);
                tvpParam.SqlDbType = SqlDbType.Structured;
                tvpParam.TypeName = udtName;

                try
                {
                    sqlConn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    flag = false;
                    Logger.InfoLog.Error(string.Format("执行存储过程{1}时错误：{0}", ex.Message, procedureName));
                }
                finally
                {
                    sqlConn.Close();
                }
            }
            
            return flag;
        }

        public static bool TVPUpdate_Retry_Split(DataTable dt, string procedureName, string udtName, string dbstring)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return false;
            }

            bool success = true;
            int rowNum = dt.Rows.Count;
            DataTable splitdt = dt.Clone();
            for (int i = 0; i < rowNum; i++)
            {
                splitdt.ImportRow(dt.Rows[i]);
                if (splitdt.Rows.Count >= 1000 || i == rowNum - 1)
                {
                    bool good = TVPUpdate_Retry(splitdt, procedureName, udtName, dbstring);
                    if (!good)
                    {
                        success = false;
                        Logger.InfoLog.Error(string.Format("TVPUpdate_Retry_Split出错：{0},第{1}项之前", procedureName, i));
                        break;
                    }
                    splitdt.Clear();
                }
            }
            return success;
        }

        public static bool TVPUpdate_Retry(DataTable dt, string procedureName, string udtName, string dbstring)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return false;
            }

            bool flag = false;

            using(SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings[dbstring].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(procedureName, sqlConn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 120;

                SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpNew", dt);
                tvpParam.SqlDbType = SqlDbType.Structured;
                tvpParam.TypeName = udtName;

                int retry = 5;
                for (int i = 0; i < retry; i++)
                {
                    try
                    {
                        sqlConn.Open();
                        cmd.ExecuteNonQuery();//曾出现过超过2分钟而不结束的情况，但会在约1小时后抛异常退出
                        flag = true;

                    }
                    catch (Exception ex)
                    {
                        Logger.InfoLog.Warn(string.Format("执行存储过程{1}时错误：{0}, retry:{2}", ex.Message, procedureName, i));
                    }
                    finally
                    {
                        sqlConn.Close();
                    }
                    if (flag)
                    {
                        break;
                    }
                }
            }
            
            if (flag)
            {
                Logger.InfoLog.Info(string.Concat(procedureName, "执行成功"));
            }
            else
            {
                Logger.InfoLog.Error(string.Concat(procedureName, "执行5次均出错"));
            }
            return flag;
        }
        //public static void TVPUpdateDB(DataTable dt, string procedureName, string udtName, string dbstring)
        //{
        //    Database db = DB.TaskWriter;
        //    DbCommand cmd = db.GetStoredProcCommand(procedureName);
        //    cmd.CommandType = CommandType.StoredProcedure;
        //    //DbParameter tvpParam = cmd.CreateParameter();
        //    //tvpParam.DbType = DbType.Currency;
        //    SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpNew", dt);
        //    tvpParam.SqlDbType = SqlDbType.Structured;
        //    tvpParam.TypeName = udtName;

        //    cmd.Parameters.Add(
        //    db.ExecuteNonQuery(cmd);
        //}

        public static int GetLastID(string SiteStatusName)
        {
            int result = -1;
            int.TryParse(GetLastStatusValue(SiteStatusName), out result);
            return result;
        }

        public static string GetLastStatusValue(string SiteStatusName)
        {
            Database db = DB.ZxbReader;
            string sql = "SELECT StatusValue from SiteStatus where StatusName=@StatusName";
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            db.AddInParameter(dbCommand, "@StatusName", DbType.String, SiteStatusName);
            return db.ExecuteScalar(dbCommand).ToString();
        }

        public static int UpdateLastStatusValue(string SiteStatusName, string SiteStatusValue)
        {
            Database db = DB.ZxbWriter;
            string sql = "UPDATE SiteStatus SET StatusValue=@StatusValue where StatusName=@StatusName";
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            db.AddInParameter(dbCommand, "@StatusName", DbType.String, SiteStatusName);
            db.AddInParameter(dbCommand, "@StatusValue", DbType.String, SiteStatusValue);
            return db.ExecuteNonQuery(dbCommand);
        }

        public static int InsertSiteStat(string statname, DateTime statday)
        {
            Database db = DB.ZxbWriter;
            string sql = "insert into SiteStat (statName,statDate) values(@statName,@statDate)";
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            db.AddInParameter(dbCommand, "@statName", DbType.String, statname);
            db.AddInParameter(dbCommand, "@statDate", DbType.DateTime, statday);
            return db.ExecuteNonQuery(dbCommand);
        }

        //依赖dt的NewHouseRealEstateID排序
        public static bool TVPInsertEstate(DataTable dt, NorthSouth ns)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return false;
            }

            bool flag = true;
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ZxbWriter"].ConnectionString);

            SqlCommand cmd = new SqlCommand("EstateManageImport", sqlConn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpNew", dt);
            tvpParam.SqlDbType = SqlDbType.Structured;
            tvpParam.TypeName = "dbo.UdtEstateManage";
            SqlParameter statusParam = cmd.Parameters.AddWithValue("@StatusName", Constant.DicNorthSouth[ns].StatusName);
            statusParam.SqlDbType = SqlDbType.NVarChar;

            try
            {
                sqlConn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                flag = false;
                Logger.InfoLog.Error(string.Format("错误：{0}, 发生在新房库记录{1}与{2}之间 ", ex.Message, dt.Rows[0]["NewHouseRealEstateID"], dt.Rows[dt.Rows.Count - 1]["NewHouseRealEstateID"]));
            }
            finally
            {
                sqlConn.Close();
            }
            return flag;
        }

        public static bool TVPUpdate_Sql_Retry_Split(DataTable dt, string procedureSql, string udtName, string dbstring)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return false;
            }

            bool success = true;
            int rowNum = dt.Rows.Count;
            DataTable splitdt = dt.Clone();
            for (int i = 0; i < rowNum; i++)
            {
                splitdt.ImportRow(dt.Rows[i]);
                if (splitdt.Rows.Count >= 1000 || i == rowNum - 1)
                {
                    bool good = TVPUpdate_Sql_Retry(splitdt, procedureSql, udtName, dbstring);
                    if (!good)
                    {
                        success = false;
                        Logger.InfoLog.Error(string.Format("TVPUpdate_Retry_Split出错：{0},第{1}项之前", procedureSql, i));
                        break;
                    }
                    splitdt.Clear();
                }
            }
            return success;
        }
        public static bool TVPUpdate_Sql_Retry(DataTable dt, string procedureSql, string udtName, string dbstring)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return false;
            }

            bool flag = false;

            using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings[dbstring].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(procedureSql, sqlConn);
                //cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 120;

                SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpNew", dt);
                tvpParam.SqlDbType = SqlDbType.Structured;
                tvpParam.TypeName = udtName;

                int retry = 5;
                for (int i = 0; i < retry; i++)
                {
                    try
                    {
                        sqlConn.Open();
                        cmd.ExecuteNonQuery();//曾出现过超过2分钟而不结束的情况，但会在约1小时后抛异常退出
                        flag = true;

                    }
                    catch (Exception ex)
                    {
                        Logger.InfoLog.Warn(string.Format("执行存储过程{1}时错误：{0}, retry:{2}", ex.Message, procedureSql, i));
                    }
                    finally
                    {
                        sqlConn.Close();
                    }
                    if (flag)
                    {
                        break;
                    }
                }
            }

            if (flag)
            {
                Logger.InfoLog.Info(string.Concat(procedureSql, "执行成功"));
            }
            else
            {
                Logger.InfoLog.Error(string.Concat(procedureSql, "执行5次均出错"));
            }
            return flag;
        }
    }
}
